﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using LantinxShare.LBS;
using System.Data;
using LantixLBSServer.Web.DbAccess.Helper;
using LantinxShare.Common;

namespace LantixLBSServer.Web.DbAccess
{
    public class MarkManager : IManager
    {
        public List<GMark> GetMarks()
        {
            List<GMark> list = new List<GMark>();
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                IDataReader reader = db.ExecuteDataReader(@"select id,MarkName,Address,Description,Longitude,Latitude,Altitude,
                                        Radius,West,South,East,North from t_mark", false);
                while (reader.Read())
                {
                    GMark gmark = new GMark()
                    {
                        Id = reader.GetInt32(0),
                        MarkName = reader.GetString(1),
                        Address = reader.GetString(2),
                        Description = reader.GetString(3),
                        Longitude = reader.GetDouble(4),
                        Latitude = reader.GetDouble(5),
                        Altitude = reader.GetDouble(6),
                        Radius = reader.GetDouble(7),
                        West = reader.GetDouble(8),
                        South = reader.GetDouble(9),
                        East = reader.GetDouble(10),
                        North = reader.GetDouble(11)
                    };
                    list.Add(gmark);
                }

            }, () => { db.CloseConnection(); });
            return list;
        }

        public GMark GetMarkByMarkName(String markName)
        {
            GMark gmark = null;
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@markname");
                db.SetParameterValue("@markname", string.Format("%{0}%", markName));
                IDataReader reader = db.ExecuteDataReader(@"select id,MarkName,Address,Description,Longitude,Latitude,Altitude,
                                        Radius,West,South,East,North from t_mark where markname like @markname", false);
                while (reader.Read())
                {
                    gmark = new GMark()
                    {
                        Id = reader.GetInt32(0),
                        MarkName = reader.GetString(1),
                        Address = reader.GetString(2),
                        Description = reader.GetString(3),
                        Longitude = reader.GetDouble(4),
                        Latitude = reader.GetDouble(5),
                        Altitude = reader.GetDouble(6),
                        Radius = reader.GetDouble(7),
                        West = reader.GetDouble(8),
                        South = reader.GetDouble(9),
                        East = reader.GetDouble(10),
                        North = reader.GetDouble(11)
                    };
                    break;
                }

            }, () => { db.CloseConnection(); });

            return gmark;
        }

        public GMark GetMarkByAddress(String address)
        {
            GMark gmark = null;
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@address");
                db.SetParameterValue("@address", string.Format("%{0}%", address));
                IDataReader reader = db.ExecuteDataReader(@"select id,MarkName,Address,Description,Longitude,Latitude,Altitude,
                                        Radius,West,South,East,North from t_mark where address like @address", false);
                while (reader.Read())
                {
                    gmark = new GMark()
                    {
                        Id = reader.GetInt32(0),
                        MarkName = reader.GetString(1),
                        Address = reader.GetString(2),
                        Description = reader.GetString(3),
                        Longitude = reader.GetDouble(4),
                        Latitude = reader.GetDouble(5),
                        Altitude = reader.GetDouble(6),
                        Radius = reader.GetDouble(7),
                        West = reader.GetDouble(8),
                        South = reader.GetDouble(9),
                        East = reader.GetDouble(10),
                        North = reader.GetDouble(11)
                    };
                    break;
                }

            }, () => { db.CloseConnection(); });

            return gmark;
        }

        public GMark GetMarkByMark(GMark gmark)
        {
            
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@MarkName");
                db.SetParameterValue("@MarkName", gmark.MarkName);
                db.AddParameter("@Address");
                db.SetParameterValue("@Address", gmark.Address);
                db.AddParameter("@Description");
                db.SetParameterValue("@Description", gmark.Description);
                db.AddParameter("@Longitude");
                db.SetParameterValue("@Longitude", gmark.Longitude);
                db.AddParameter("@Latitude");
                db.SetParameterValue("@Latitude", gmark.Latitude);
                db.AddParameter("@Altitude");
                db.SetParameterValue("@Altitude", gmark.Altitude);
                db.AddParameter("@Radius");
                db.SetParameterValue("@Radius", gmark.Radius);
                db.AddParameter("@West");
                db.SetParameterValue("@West", gmark.West);
                db.AddParameter("@South");
                db.SetParameterValue("@South", gmark.South);
                db.AddParameter("@East");
                db.SetParameterValue("@East", gmark.East);
                db.AddParameter("@North");
                db.SetParameterValue("@North", gmark.North);
                IDataReader reader = db.ExecuteDataReader(@"select id from t_mark where MarkName=@MarkName and Address=@Address and
                        Description=@Description and Longitude = @Longitude and 
                    Latitude=@Latitude and Altitude=@Altitude and Radius=@Radius and 
                    West=@West and South=@South and East=@East and North=@North",false);
                while (reader.Read())
                {
                    gmark.Id = reader.GetInt32(0);
                    break;
                }
            }, () => { db.CloseConnection(); });
            return gmark;
        }

        public bool UpdateMark(GMark user)
        {
            return false;
        }

        public bool InsertMark(GMark gmark)
        {
            bool result = false;
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@MarkName");
                db.SetParameterValue("@MarkName", gmark.MarkName);
                db.AddParameter("@Address");
                db.SetParameterValue("@Address", gmark.Address);
                db.AddParameter("@Description");
                db.SetParameterValue("@Description", gmark.Description);
                db.AddParameter("@Longitude");
                db.SetParameterValue("@Longitude", gmark.Longitude);
                db.AddParameter("@Latitude");
                db.SetParameterValue("@Latitude", gmark.Latitude);
                db.AddParameter("@Altitude");
                db.SetParameterValue("@Altitude", gmark.Altitude);
                db.AddParameter("@Radius");
                db.SetParameterValue("@Radius", gmark.Radius);
                db.AddParameter("@West");
                db.SetParameterValue("@West", gmark.West);
                db.AddParameter("@South");
                db.SetParameterValue("@South", gmark.South);
                db.AddParameter("@East");
                db.SetParameterValue("@East", gmark.East);
                db.AddParameter("@North");
                db.SetParameterValue("@North", gmark.North);
                if (db.ExecuteNonQuery(@"insert into t_mark(MarkName,Address,Description,Longitude,Latitude,Altitude,Radius,West,South,East,North) 
                        values(@MarkName,@Address,@Description,@Longitude,@Latitude,@Altitude,@Radius,@West,@South,@East,@North)", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }

        public bool DeleteMarkById(int id)
        {
            bool result = false;
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@id");
                db.SetParameterValue("@id", id);
                if (db.ExecuteNonQuery(@"delete from t_mark where id =@id ", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }
    }
}